package com.example.jdbc.test;


import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.example.jdbc.Brand;
import com.example.jdbc.DruidUtil;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCExampleTest2 {


    @Test
    public void testSelectAll() throws Exception {
/**
 该例通过连接池获取数据库连接
 */
        //加载连接池配置文件
//        Properties prop = new Properties();
//        //使用流读取resources目录下druid.properties文件的内容
//        InputStream in = JDBCExampleTest.class.getClassLoader().getResourceAsStream("druid.properties");
//        //加载
//        prop.load(in);
//        //获取连接池对象
//        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//        //获取数据库连接 Connection
//        Connection conn = dataSource.getConnection();
        Connection conn = DruidUtil.getConn();
        //定义SQL
        String sql = "select * from tb_brand;";
        //获取PreparedStatement对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //执行SQL
        ResultSet rs = pstmt.executeQuery();
        // 处理结果 List<Brand> 封装Brand对象，装载List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        while (rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");
            //封装Brand对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);
            //装载集合
            brands.add(brand);
        }
        //输出集合内容
        System.out.println(brands);
        //释放资源
        DruidUtil.close(conn,pstmt,rs);
//        rs.close();
//        pstmt.close();
//        //使用完后必须 conn.close() 掉 ，使用连接池的话，执行 conn.close 并不会关闭与数据库的 TCP 连接，而是将连接还回到池中去，如果不 close 掉的话，这个连接将会一直被占用，直接连接池中的连接耗尽为止。
//        conn.close();
    }

    //增加
    @Test
    public void insert() throws Exception {

        //加载连接池配置文件
        Properties prop = new Properties();
        //使用流读取resources目录下druid.properties文件的内容
        InputStream in = JDBCExampleTest.class.getClassLoader().getResourceAsStream("druid.properties");
        //加载
        prop.load(in);
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        //定义插入操作的SQL
        String insertSQL= "INSERT INTO tb_brand(id,brand_name,company_name,ordered,description,status) " +
                "VALUES(?,?,?,?,?,?)";
        //获取SQL执行对象
        PreparedStatement preparedStatement = conn.prepareStatement(insertSQL);
        //给SQL语句的占位符？设置值
        preparedStatement.setInt(1,5);
        preparedStatement.setString(2,"iPhone");
        preparedStatement.setString(3,"苹果公司");
        preparedStatement.setInt(4,6000);
        preparedStatement.setString(5,"市值最高的科技公司");
        preparedStatement.setInt(6,1);
        //执行
        int count = preparedStatement.executeUpdate();
        System.out.println(count>0?"添加成功":"插入失败");
        //释放资源
        preparedStatement.close();
        conn.close();


    }

    @Test
    public  void update() throws Exception {

        //加载连接池配置文件
        Properties prop = new Properties();
        //使用流读取resources目录下druid.properties文件的内容
        InputStream in = JDBCExampleTest.class.getClassLoader().getResourceAsStream("druid.properties");
        //加载
        prop.load(in);
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        //定义更新操作的SQL
        String updateSQL= "UPDATE tb_brand SET description = ? WHERE id=5";
        //获取SQL执行对象
        PreparedStatement preparedStatement = conn.prepareStatement(updateSQL);
        //给SQL语句的占位符？设置值
        preparedStatement.setString(1,"市值非常高的公司");
        //执行
        int count = preparedStatement.executeUpdate();

        System.out.println(count>0?"修改成功":"修改失败");
        //释放资源
        preparedStatement.close();
        conn.close();
    }

    //删除

    @Test
    public  void deleteTest() throws Exception {
//        //加载连接池配置文件
//        Properties prop = new Properties();
//        //使用流读取resources目录下druid.properties文件的内容
//        InputStream in = JDBCExampleTest.class.getClassLoader().getResourceAsStream("druid.properties");
//        //加载
//        prop.load(in);
//        //获取连接池对象
//        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//        //获取数据库连接 Connection
//        Connection conn = dataSource.getConnection();
        Connection conn = DruidUtil.getConn();
        //定义删除操作的SQL
        String deleteSQL= "DELETE FROM tb_brand WHERE id =?";
        //获取SQL执行对象
        PreparedStatement preparedStatement = conn.prepareStatement(deleteSQL);
        //给SQL语句的占位符？设置值
        preparedStatement.setInt(1,4);
        //执行
        int count = preparedStatement.executeUpdate();

        System.out.println(count>0?"删除成功":"删除失败");
        //释放资源
        DruidUtil.close(conn,preparedStatement);
//        preparedStatement.close();
//        conn.close();
    }








}
